﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Gencode.Common.SQL;
using Gencode.PrsCommon.App;

namespace Gencode.PrsCommon.Data.Plan.Termin
{
	public class TerminData
	{
		/// <summary>
		/// [terminid,terminname,planid,prsid,datum,abteilungid]
		/// </summary>
		/// <param name="planid"></param>
		/// <param name="mandid"></param>
		/// <param name="planterminid"></param>
		/// <returns></returns>
		public static DataView GetPlanTermine(int planid, string mandid, int planterminid)
		{
			Querry query = new Querry();
			query.SetSTPName("get_plantermine");
			query.AddParameter("@planid", planid);
			query.AddParameter("@mandid", mandid);
			query.AddParameter("@planterminid", planterminid);

			return query.ExecuteForDataView();
		}

		/// <summary>
		/// [PrsId,Vorname,Nachname,AbteilungID,Anzeigename]
		/// </summary>
		/// <param name="terminid">terminid</param>
		/// <returns>[PrsId,Vorname,Nachname,Anzeigename]</returns>
		public static DataView GetPersonenNotInTermin(int planid, int planterminid)
		{
			Querry query = new Querry("get_personnotintermin");
			
			query.AddParameter("@planid", planid);
			query.AddParameter("@planterminid", planterminid);
			
			return query.ExecuteForDataView();
		}

		/// <summary>
		/// [PrsId,Vorname,Nachname,AbteilungID,Anzeigename]
		/// </summary>
		/// <param name="terminid">terminid</param>
		/// <returns>[PrsId,Vorname,Nachname,AbteilungID,Anzeigename]</returns>
		public static DataView GetPersonenInTermin(int planterminid, int planid)
		{
			return SimpleQuerry.ExecuteForDataView("select p.PrsId,Vorname,Nachname, a.AbteilungID, Anzeigename=Nachname + ' '+ Vorname + ' - ' + a.Name from Person p "
				+ "inner join Terminplan on Terminplan.PrsID = p.PrsId inner join Abteilung a on a.AbteilungID = Terminplan.AbteilungID "
				+ "where PlanTerminID = " + planterminid + " and PlanID = " + planid + " order by a.AbteilungID, p.PrsId");
		}

		/// <summary>
		/// [TerminID,Name,RGB,SHG]
		/// </summary>
		/// <param name="mandid">mandid</param>
		/// <returns>[TerminID,Name,RGB,SHG]</returns>
		public static DataView GetTerminArten(string mandid)
		{
			return SimpleQuerry.ExecuteForDataView("select TerminID,Name,RGB=DefRGB,SHG from Termin where Status = 1 and mandid = '" + mandid + "'");
		}

		/// <summary>
		/// [PlanTerminID,PrsID,Datum,AbteilungID,TerminID,Name]
		/// </summary>
		/// <param name="planid">planid</param>
		/// <returns>[PlanTerminID,PrsID,Datum,AbteilungID,TerminID,Name]</returns>
		public static DataView GetPlanTermine(int planid)
		{
			return SimpleQuerry.ExecuteForDataView("select PlanTerminID,Datum,TerminID,Name from Terminplan where PlanID = " + planid + " group by PlanTerminID,Datum,TerminID,Name");
		}

		public static void DeleteTerminPlan(int terminplanid, int planid)
		{
            Gencode.Common.ActionLogger.Logger.LoggMessage(Gencode.Common.ActionLogger.MessageType.SQL,
                "delete from Terminplan where PlanTerminID = " + terminplanid + " and PlanID = " + planid);
			SimpleQuerry.Execute("delete from Terminplan where PlanTerminID = " + terminplanid + " and PlanID = " + planid);
		}

		public static int GetMaxPlanTerminID(int planid)
		{
			return int.Parse(SimpleQuerry.ExecuteForDataView("select isnull(max(PlanTerminID + 1),1) from Terminplan where PlanID = " + planid)[0].Row[0].ToString());
		}

		public static void AddPlanTermin(int planterminid, int planid, int prsid, string name, string date, int abteilungid, int terminid)
		{
			Querry stp = new Querry();

			stp.SetSTPName("set_terminplan");

			stp.AddParameter("@planterminid", planterminid);
			stp.AddParameter("@planID", planid);
			stp.AddParameter("@prsID", prsid);
			stp.AddParameter("@name", name);
			stp.AddParameter("@datum", DateUtil.ConvertDateToSQL(date));
			stp.AddParameter("@abteilungID", abteilungid);
			stp.AddParameter("@terminid", terminid);

			stp.Execute(true);
		}
	}
}
